/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.util.Calendar;
import java.sql.*;
import java.util.Date;

import java.text.*;
import java.util.Vector;

/**
 *
 * @author Venus
 */
public class LSGia_Model {

    public Vector LayDSLSG() {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from lichsugia Order by MaLSG desc";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LSGia lsg = new Beans.LSGia();
                lsg.setMaLSG(rss.getInt(1));
                lsg.setNgayGio(rss.getString(2));
                lsg.setMaMA(rss.getInt(3));
                lsg.setMaDVT(rss.getInt(4));
                lsg.setGia(rss.getFloat(5));
                v.add(lsg);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean DeleteLSG(String malsg) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Delete from lichsugia where MaLSG = " + malsg;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector SortNgayGioLSG(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from lichsugia Order by NgayGio " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LSGia lsg = new Beans.LSGia();
                lsg.setMaLSG(rss.getInt(1));
                lsg.setNgayGio(rss.getString(2));
                lsg.setMaMA(rss.getInt(3));
                lsg.setMaDVT(rss.getInt(4));
                lsg.setGia(rss.getFloat(5));
                v.add(lsg);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortMonAnLSG(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaLSG), NgayGio, A.MaMA, A.MaDVT, A.Gia from lichsugia A, monan B "
                    + " where A.MaMA = B.MaMA Order by TenMA " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LSGia lsg = new Beans.LSGia();
                lsg.setMaLSG(rss.getInt(1));
                lsg.setNgayGio(rss.getString(2));
                lsg.setMaMA(rss.getInt(3));
                lsg.setMaDVT(rss.getInt(4));
                lsg.setGia(rss.getFloat(5));
                v.add(lsg);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortDVTLSG(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaLSG), NgayGio, A.MaMA, A.MaDVT, A.Gia from lichsugia A, donvitinh B "
                    + " where A.MaDVT = B.MaDVT Order by TenDVT " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LSGia lsg = new Beans.LSGia();
                lsg.setMaLSG(rss.getInt(1));
                lsg.setNgayGio(rss.getString(2));
                lsg.setMaMA(rss.getInt(3));
                lsg.setMaDVT(rss.getInt(4));
                lsg.setGia(rss.getFloat(5));
                v.add(lsg);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortGiaLSG(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from lichsugia Order by Gia " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LSGia lsg = new Beans.LSGia();
                lsg.setMaLSG(rss.getInt(1));
                lsg.setNgayGio(rss.getString(2));
                lsg.setMaMA(rss.getInt(3));
                lsg.setMaDVT(rss.getInt(4));
                lsg.setGia(rss.getFloat(5));
                v.add(lsg);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SearchLSG(String tungay, String tugio, String tuphut, String denngay, String dengio, String denphut, String tenmonan, String tendvt, String gia, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int giotu = Integer.parseInt(tugio);
            int phuttu = Integer.parseInt(tuphut);
            int gioden = Integer.parseInt(dengio);
            int phutden = Integer.parseInt(denphut);
            String rsdate = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngaygio from lichsugia ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    String z = ngaygio[1];                    
                    String hoursinfo[] = z.split(":");
                    int gio = Integer.parseInt(hoursinfo[0]);
                    int phut = Integer.parseInt(hoursinfo[1]);
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu) {
                                rsdate = rsdate + x + ";";
                            }

                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == gioden && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu && gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                }
            }
            String query = " select distinct(MaLSG), NgayGio, A.MaMA, A.MaDVT, A.Gia from lichsugia A, monan B, donvitinh C "
                    + " where A.MaMA = B.MaMA and A.MaDVT = C.MaDVT and B.MaNN = " + langid + " ";
            String[] arr = {tenmonan, tendvt, gia};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenmonan)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(tendvt)) {
                    query = query + " and TenDVT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(gia)) {
                    query = query + " and A.Gia = " + arr[i] + " ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String x = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                query = query + " and NgayGio in (" + datequery + ") ";
            }
            query = query + " Order by MaLSG desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LSGia lsg = new Beans.LSGia();
                lsg.setMaLSG(rss.getInt(1));
                lsg.setNgayGio(rss.getString(2));
                lsg.setMaMA(rss.getInt(3));
                lsg.setMaDVT(rss.getInt(4));
                lsg.setGia(rss.getFloat(5));
                v.add(lsg);
            }
            stmt.close();
            rss.close();
            
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchNgayGioLSG(String tungay, String tugio, String tuphut, String denngay, String dengio, String denphut, String tenmonan, String tendvt, String gia, int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int giotu = Integer.parseInt(tugio);
            int phuttu = Integer.parseInt(tuphut);
            int gioden = Integer.parseInt(dengio);
            int phutden = Integer.parseInt(denphut);
            String rsdate = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngaygio from lichsugia ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    String z = ngaygio[1];
                    String hoursinfo[] = z.split(":");
                    int gio = Integer.parseInt(hoursinfo[0]);
                    int phut = Integer.parseInt(hoursinfo[1]);
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu) {
                                rsdate = rsdate + x + ";";
                            }

                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == gioden && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu && gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                }
            }
            String query = " select distinct(MaLSG), NgayGio, A.MaMA, A.MaDVT, A.Gia from lichsugia A, monan B, donvitinh C "
                    + " where A.MaMA = B.MaMA and A.MaDVT = C.MaDVT and B.MaNN = " + langid + " ";
            String[] arr = {tenmonan, tendvt, gia};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenmonan)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(tendvt)) {
                    query = query + " and TenDVT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(gia)) {
                    query = query + " and A.Gia = " + arr[i] + " ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String x = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                query = query + " and NgayGio in (" + datequery + ") ";
            }
            query = query + " Order by NgayGio " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LSGia lsg = new Beans.LSGia();
                lsg.setMaLSG(rss.getInt(1));
                lsg.setNgayGio(rss.getString(2));
                lsg.setMaMA(rss.getInt(3));
                lsg.setMaDVT(rss.getInt(4));
                lsg.setGia(rss.getFloat(5));
                v.add(lsg);
            }
            stmt.close();
            rss.close();
            
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchMonAnLSG(String tungay, String tugio, String tuphut, String denngay, String dengio, String denphut, String tenmonan, String tendvt, String gia, int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int giotu = Integer.parseInt(tugio);
            int phuttu = Integer.parseInt(tuphut);
            int gioden = Integer.parseInt(dengio);
            int phutden = Integer.parseInt(denphut);
            String rsdate = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngaygio from lichsugia ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    String z = ngaygio[1];
                    String hoursinfo[] = z.split(":");
                    int gio = Integer.parseInt(hoursinfo[0]);
                    int phut = Integer.parseInt(hoursinfo[1]);
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == gioden && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu && gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                }
            }
            String query = " select distinct(MaLSG), NgayGio, A.MaMA, A.MaDVT, A.Gia from lichsugia A, monan B, donvitinh C "
                    + " where A.MaMA = B.MaMA and A.MaDVT = C.MaDVT and B.MaNN = " + langid + " ";
            String[] arr = {tenmonan, tendvt, gia};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenmonan)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(tendvt)) {
                    query = query + " and TenDVT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(gia)) {
                    query = query + " and A.Gia = " + arr[i] + " ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String x = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                query = query + " and NgayGio in (" + datequery + ") ";
            }
            query = query + " Order by TenMA " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LSGia lsg = new Beans.LSGia();
                lsg.setMaLSG(rss.getInt(1));
                lsg.setNgayGio(rss.getString(2));
                lsg.setMaMA(rss.getInt(3));
                lsg.setMaDVT(rss.getInt(4));
                lsg.setGia(rss.getFloat(5));
                v.add(lsg);
            }
            stmt.close();
            rss.close();
            
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchDVTLSG(String tungay, String tugio, String tuphut, String denngay, String dengio, String denphut, String tenmonan, String tendvt, String gia, int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int giotu = Integer.parseInt(tugio);
            int phuttu = Integer.parseInt(tuphut);
            int gioden = Integer.parseInt(dengio);
            int phutden = Integer.parseInt(denphut);
            String rsdate = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngaygio from lichsugia ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    String z = ngaygio[1];
                    String hoursinfo[] = z.split(":");
                    int gio = Integer.parseInt(hoursinfo[0]);
                    int phut = Integer.parseInt(hoursinfo[1]);
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu) {
                                rsdate = rsdate + x + ";";
                            }

                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == gioden && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu && gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                }
            }
            String query = " select distinct(MaLSG), NgayGio, A.MaMA, A.MaDVT, A.Gia from lichsugia A, monan B, donvitinh C "
                    + " where A.MaMA = B.MaMA and A.MaDVT = C.MaDVT and B.MaNN = " + langid + " ";
            String[] arr = {tenmonan, tendvt, gia};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenmonan)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(tendvt)) {
                    query = query + " and TenDVT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(gia)) {
                    query = query + " and A.Gia = " + arr[i] + " ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String x = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                query = query + " and NgayGio in (" + datequery + ") ";
            }
            query = query + " Order by TenDVT " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LSGia lsg = new Beans.LSGia();
                lsg.setMaLSG(rss.getInt(1));
                lsg.setNgayGio(rss.getString(2));
                lsg.setMaMA(rss.getInt(3));
                lsg.setMaDVT(rss.getInt(4));
                lsg.setGia(rss.getFloat(5));
                v.add(lsg);
            }
            stmt.close();
            rss.close();
            
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchGiaLSG(String tungay, String tugio, String tuphut, String denngay, String dengio, String denphut, String tenmonan, String tendvt, String gia, int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int giotu = Integer.parseInt(tugio);
            int phuttu = Integer.parseInt(tuphut);
            int gioden = Integer.parseInt(dengio);
            int phutden = Integer.parseInt(denphut);
            String rsdate = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngaygio from lichsugia ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    String z = ngaygio[1];
                    String hoursinfo[] = z.split(":");
                    int gio = Integer.parseInt(hoursinfo[0]);
                    int phut = Integer.parseInt(hoursinfo[1]);
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu) {
                                rsdate = rsdate + x + ";";
                            }

                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == gioden && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu && gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                }
            }
            String query = " select distinct(MaLSG), NgayGio, A.MaMA, A.MaDVT, A.Gia from lichsugia A, monan B, donvitinh C "
                    + " where A.MaMA = B.MaMA and A.MaDVT = C.MaDVT and B.MaNN = " + langid + " ";
            String[] arr = {tenmonan, tendvt, gia};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenmonan)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(tendvt)) {
                    query = query + " and TenDVT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(gia)) {
                    query = query + " and A.Gia = " + arr[i] + " ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String x = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                query = query + " and NgayGio in (" + datequery + ") ";
            }
            query = query + " Order by A.Gia " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LSGia lsg = new Beans.LSGia();
                lsg.setMaLSG(rss.getInt(1));
                lsg.setNgayGio(rss.getString(2));
                lsg.setMaMA(rss.getInt(3));
                lsg.setMaDVT(rss.getInt(4));
                lsg.setGia(rss.getFloat(5));
                v.add(lsg);
            }
            stmt.close();
            rss.close();
            
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
}
